﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Supermarket.Dal
{
    public static class CategoryDB
    {
        public static List<Category> GetAllCategories()
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var categories =
                from cat in db.Categories
                select cat;

            return categories.ToList<Category>();
        }

        public static void Update(int id, string name)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var category =
                from cat in db.Categories
                where cat.CategoryId == id
                select cat;

            category.First().CategoryName = name;
            db.SubmitChanges();
        }

        public static void CreateCategory(Category category)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            db.Categories.InsertOnSubmit(category);
            db.SubmitChanges();
        }

        public static void Delete(int id)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var deleteCategory =
                from cat in db.Categories
                where cat.CategoryId == id
                select cat;
            if (deleteCategory.Count() > 0)
            {
                db.Categories.DeleteOnSubmit(deleteCategory.First());
                db.SubmitChanges();
            }
        }

        public static Category GetItem(int id)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var category =
                from cat in db.Categories
                where cat.CategoryId == id
                select cat;
            if (category.Count() > 0)
            {
                return category.First();
            }

            return null;
        }

        public static int GetProductCount(Category category)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            
            if (category != null)
            {
                var productCount =
                    from cat in db.Categories
                    join prod in db.Products on cat.CategoryId equals prod.CategoryId
                    where prod.CategoryId == category.CategoryId
                    group prod by prod.ProductName into g
                    select g.Count();

                try
                {
                    return productCount.First();
                }
                catch (InvalidOperationException)
                {
                    return 0;
                }
            }

            return 0;
        }
    }
}
